﻿CREATE PROCEDURE [dbo].[TempArticles_CombineRepeatedNamesAndUpdateSales]
	@ArticleCodes varchar(max)
AS

-- Guardo las combinaciones OriginalCode/NewCode en una temporal
Select Code, NewCode Into #Codes
From
(Select
	Code = (Select SUBSTRING(Id,1,CHARINDEX(',', Id) - 1)),
	NewCode = (Select SUBSTRING(Id,CHARINDEX(',', Id) + 1,LEN(Id) + 1))
From (Select Id From dbo.SplitStrings(@ArticleCodes, ';')) a )b


declare @NewCode varchar(50);

Declare c Cursor For Select distinct NewCode From #Codes
Open c

Fetch next From c into @NewCode


--Por cada targetCode
While @@Fetch_Status=0 
Begin
	--Seteo los registros de ventas como Confirmed en false
	Update TempArticleSales
	Set Confirmed = 0
	Where ArticleCode in (select Code from #Codes where NewCode = @NewCode
							union select @NewCode)

	--Inserto ventas sumarizadas
	Insert Into TempArticleSales (StoreId, Date, ArticleCode, ArticleName, Units, Sales, GM, GrossSales, Price, Confirmed, 
				RangeReviewId, CategoryId, EAN, UOM, Brand, Manufacturer, NotFoundCategory, Size, Width, Height, Facings, Attribute1, 
				Attribute2, Attribute3, Attribute4, Attribute5, TaxRate, CountryId, StoreCode, StoreName, Region, City, Address, Phone, Fax, Format)
	SELECT
		 --Sales
		 StoreId, 
		 Date, 
		 ArticleCode = @NewCode, 
		 ArticleName as ArticleName,  
		 SUM(Units) as Units, 
		 SUM(Sales)as Sales, 
		 SUM(GM)as GM, 
		 SUM(GrossSales)as GrossSales, 
		 case when SUM(Units) > 0 then SUM(GrossSales) / SUM(Units) else 0 end as Price, 
		 1 as Confirmed,
		 --Article
		 RangeReviewId = (select top 1 RangeReviewId from TempArticleSales where ArticleCode = @NewCode),
		 CategoryId = (select top 1 CategoryId from TempArticleSales where ArticleCode = @NewCode),
		 EAN = (select top 1 EAN from TempArticleSales where ArticleCode = @NewCode),
		 UOM = (select top 1 UOM from TempArticleSales where ArticleCode = @NewCode),
		 Brand = (select top 1 Brand from TempArticleSales where ArticleCode = @NewCode),
		 Manufacturer = (select top 1 Manufacturer from TempArticleSales where ArticleCode = @NewCode),
		 NotFoundCategory = (select top 1 NotFoundCategory from TempArticleSales where ArticleCode = @NewCode),
		 Size = (select top 1 Size from TempArticleSales where ArticleCode = @NewCode),
		 Width = (select top 1 Width from TempArticleSales where ArticleCode = @NewCode),
		 Height = (select top 1 Height from TempArticleSales where ArticleCode = @NewCode),
		 Facings = (select top 1 Facings from TempArticleSales where ArticleCode = @NewCode),
		 Attribute1 = (select top 1 Attribute1 from TempArticleSales where ArticleCode = @NewCode),
		 Attribute2 = (select top 1 Attribute2 from TempArticleSales where ArticleCode = @NewCode),
		 Attribute3 = (select top 1 Attribute3 from TempArticleSales where ArticleCode = @NewCode),
		 Attribute4 = (select top 1 Attribute4 from TempArticleSales where ArticleCode = @NewCode),
		 Attribute5 = (select top 1 Attribute5 from TempArticleSales where ArticleCode = @NewCode),
		 TaxRate = (select top 1 TaxRate from TempArticleSales where ArticleCode = @NewCode),
		 --Store
		 CountryId = (select top 1 CountryId from TempArticleSales where StoreId = ts.StoreId),
		 StoreCode = (select top 1 StoreCode from TempArticleSales where StoreId = ts.StoreId),
		 StoreName = (select top 1 StoreName from TempArticleSales where StoreId = ts.StoreId),
		 Region = (select top 1 Region from TempArticleSales where StoreId = ts.StoreId),
		 City = (select top 1 City from TempArticleSales where StoreId = ts.StoreId),
		 Address = (select top 1 Address from TempArticleSales where StoreId = ts.StoreId),
		 Phone = (select top 1 Phone from TempArticleSales where StoreId = ts.StoreId),
		 Fax = (select top 1 Fax from TempArticleSales where StoreId = ts.StoreId),
		 Format = (select top 1 Format from TempArticleSales where StoreId = ts.StoreId)   
	FROM TempArticleSales ts
	WHERE ArticleCode in (select Code from #Codes where NewCode = @NewCode
							union select @NewCode)
	GROUP BY StoreId, Date, ArticleName



   Fetch next From c into @NewCode
   
End

Close c
Deallocate c

drop table #Codes

RETURN 0